The present invention relates to techniques for performing database operations involving custom fields.
The structure of a table in a relational database system is typically defined when the table is created. For example, creating the table can involve defining the data types of fields, the names of fields, and various field constraints. However, in many situations, fields in an existing table may need to be augmented to accommodate additional data. For example, a user may desire to add user-defined fields, such as a birthday field or a spouse field, to an existing customer table that already includes a name field and a balance field.
One solution to this problem is to add user-defined fields directly to an existing table. This technique is referred to as the “alter table” technique. For example, consider a customer table defined as follows:
customercustomer_idintnamevarchar(50)balancedoubleAdding a user-defined field to the customer table to store birthdays involves executing the following Structured Query Language (SQL) query:                ALTER TABLE customer ADD birthday DATETIME;This query modifies the data structure of the customer table as follows:        
customercustomer_idintnamevarchar(50)balancedoublebirthdaydatetime
Since the birthday field is part of the customer table, an SQL query which retrieves the names and the birthdays for the customers stored in the customer table can have the following form:                SELECT name, birthday FROM customer;        
Several issues should be considered when determining whether to use the alter table technique:                (1) when a table is in the process of being altered, it is locked and no other user can access the table;        (2) query performance is good and can be improved by adding an index to the user-defined field;        (3) custom fields can be added as they are needed and can be deleted when they are no longer needed;        (4) schema changes must be propagated to every database in the database environment;        (5) user-defined field names must be prevented from conflicting with any derived table names (present or future); and        (6) if multiple users share a common schema in a hosted environment, this technique produces extremely wide tables with sparse data.        
A variation of the alter table technique assumes that all users in the database environment share the same database schema. In this case, users share custom field definitions and the database only creates a new custom field if one is not already available. For example, the customer table might already contain a user-defined integer field:
customercustomer_idintnamevarchar(50)balancedoublecustom_int_1intHence, a first user can use the field custom_int—1 to record the age of customers, while a second user can use the field custom_int—1 to record how many children a customer has. Since the custom field name no longer describes the purpose of the field, metadata can be used to provide this information.
Next, if the first user additionally desires to store the birthdays for customers in the table, an ALTER TABLE command can be used to add a custom date field to the customer table, which results in the following structure for the customer table:
customercustomer_idintnamevarchar(50)balancedoublecustom_int_1intcustom_date_1datetimeThe change to the structure of the customer table is then propagated to the database for the second user (and to other users sharing the same schema). Next, if the second user decides to create a new datetime field for the customer table, the system can reuse custom_date—1. If the change to the structure of the customer table has not been propagated to the database environment for the second user, it is propagated at this point.
The advantage of using the alter table technique is that no joins are required to retrieve the data, so the data accesses are fast. Unfortunately, as more user-defined fields are added to the existing table it becomes more difficult to manage upgrades to the existing table.